package co.fitstart.scheduler.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.demo2do.core.utils.DateFormatUtils;
import com.demo2do.core.utils.DateUtils;

import co.fitstart.scheduler.Constant;
import co.fitstart.scheduler.entity.FinishedCourseReport;
import co.fitstart.scheduler.persistence.GenericJdbcSupport;
import co.fitstart.scheduler.service.FinishedCourseReportService;

@Service("finishedCourseReportService")
public class FinishedCourseReportServiceImpl implements FinishedCourseReportService{
    
    @Autowired
    private GenericJdbcSupport genericJdbcSupport;

    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.FinishedCourseReportService#createFinishedCourseReport()
     */
    public void createFinishedCourseReport() {
        
        List<FinishedCourseReport> reports = this.getScheduleReports();
        
        reports.addAll(this.getYearScheduleReports());
        
        for(FinishedCourseReport report : reports) {
            
            String insertSentence = "INSERT INTO `finished_course_report` "
            		                     + "(`coach_name`,`is_disabled`,`occur_time`,`offer`,`student_name`,`student_mobile`,`course_id`,`site_id`,`course_schedule_id`) "
            		              + "VALUES (:coachName, false, :occurTime, :offer, :studentName, :studentMobile, :courseId, :siteId, :courseScheduleId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
        
    }
    
    public void createFinishedCourseReport(Date day) {
        List<FinishedCourseReport> reports = this.getScheduleReports(day);
        
        reports.addAll(this.getYearScheduleReports());
        
        for(FinishedCourseReport report : reports) {
            
            String insertSentence = "INSERT INTO `finished_course_report` "
                                         + "(`coach_name`,`is_disabled`,`occur_time`,`offer`,`student_name`,`student_mobile`,`course_id`,`site_id`,`course_schedule_id`) "
                                  + "VALUES (:coachName, false, :occurTime, :offer, :studentName, :studentMobile, :courseId, :siteId, :courseScheduleId)";
            Map<String, Object> insertParameters = report.describe(day);
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.FinishedCourseReportService#createFinishedCourseReport(java.util.Date, java.util.Date)
     */
    @Override
    public void createFinishedCourseReport(Date start, Date end) {
    	List<FinishedCourseReport> reports = this.getScheduleReports(start,end);
        
        reports.addAll(this.getYearScheduleReports());
        
        for(FinishedCourseReport report : reports) {
            
            String insertSentence = "INSERT INTO `finished_course_report` "
                                         + "(`coach_name`,`is_disabled`,`occur_time`,`offer`,`student_name`,`student_mobile`,`course_id`,`site_id`,`course_schedule_id`) "
                                  + "VALUES (:coachName, false, :occurTime, :offer, :studentName, :studentMobile, :courseId, :siteId, :courseScheduleId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.FinishedCourseReportService#createYearReport(java.util.Date, java.util.Date)
     */
    @Override
    public void createYearReport(Date start, Date end) {
    	// TODO Auto-generated method stub
    	Date begin = start;
    	while(begin.before(end)) {
    		List<FinishedCourseReport> reports = new ArrayList<FinishedCourseReport>();
    		reports.addAll(this.getYearScheduleReports());
    		for(FinishedCourseReport report : reports) {
                
                String insertSentence = "INSERT INTO `finished_course_report` "
                                             + "(`coach_name`,`is_disabled`,`occur_time`,`offer`,`student_name`,`student_mobile`,`course_id`,`site_id`,`course_schedule_id`) "
                                      + "VALUES (:coachName, false, :occurTime, :offer, :studentName, :studentMobile, :courseId, :siteId, :courseScheduleId)";
                Map<String, Object> insertParameters = report.describe(begin);
                
                genericJdbcSupport.executeSQL(insertSentence, insertParameters);
            }
    		begin = org.apache.commons.lang3.time.DateUtils.addDays(begin, 1);
    	}
    }
    
    /**
     * 
     * @return
     */
    private List<FinishedCourseReport> getScheduleReports() {
    	
        String sentence = "SELECT orders.price as price, "
        		               + "student.name as studentName, "
        		               + "student.mobile as studentMobile, "
        		               + "coach.name as coachName, "
        		               + "schedule.signin_time as signinTime, "
        		               + "schedule.course_id as courseId, "
        		               + "schedule.site_id as siteId, "
        		               + "schedule.id as courseScheduleId, "
        		               + "schedule.course_type as courseType, "
        		               + "attr.value as courseAmount, "
        		               + "course.hours as courseHours "
        		        + "FROM orders orders, course_schedule schedule, course_round_attr attr, "
        		             + "student student, course_round round, principal coach, course course "
        		        + "WHERE schedule.student_id = student.id AND "
        		              + "orders.id = round.order_id AND "
        		              + "round.id = attr.course_round_id AND "
        		              + "schedule.course_round_id = round.id AND "
        		              + "schedule.coach_id = coach.id AND "
        		              + "attr.name='" + Constant.FINISHED_COURSE_REPORT_COURSE_AMOUNT + "' AND "
        		              + "schedule.course_id = course.id AND "
        		              + "schedule.is_disabled = false AND "
        		              + "schedule.occur_date IS NOT NULL AND "
        		              + "schedule.signin_time IS NOT NULL AND "
        		              + "schedule.course_type in (" + Constant.FINISHED_COURSE_REPORT_COURSE_TYPE + ") AND "
        		              + "schedule.schedule_status = " + Constant.FINISHED_COURSE_REPORT_SCHEDULE_STATUS + " AND "
        		              + "schedule.signin_time BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(), FinishedCourseReport.class);
    }
    
    private List<FinishedCourseReport> getScheduleReports(Date day) {
        
        String sentence = "SELECT orders.price as price, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "coach.name as coachName, "
                               + "schedule.signin_time as signinTime, "
                               + "schedule.course_id as courseId, "
                               + "schedule.site_id as siteId, "
                               + "schedule.id as courseScheduleId, "
                               + "schedule.course_type as courseType, "
                               + "attr.value as courseAmount, "
                               + "course.hours as courseHours "
                        + "FROM orders orders, course_schedule schedule, course_round_attr attr, "
                             + "student student, course_round round, principal coach, course course "
                        + "WHERE schedule.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "round.id = attr.course_round_id AND "
                              + "schedule.course_round_id = round.id AND "
                              + "schedule.coach_id = coach.id AND "
                              + "attr.name='" + Constant.FINISHED_COURSE_REPORT_COURSE_AMOUNT + "' AND "
                              + "schedule.course_id = course.id AND "
                              + "schedule.is_disabled = false AND "
                              + "schedule.occur_date IS NOT NULL AND "
                              + "schedule.signin_time IS NOT NULL AND "
                              + "schedule.course_type in (" + Constant.FINISHED_COURSE_REPORT_COURSE_TYPE + ") AND "
                              + "schedule.schedule_status = " + Constant.FINISHED_COURSE_REPORT_SCHEDULE_STATUS + " AND "
                              + "schedule.signin_time BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(day), FinishedCourseReport.class);
    }
    
    private List<FinishedCourseReport> getScheduleReports(Date start, Date end) {
        
        String sentence = "SELECT orders.price as price, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "coach.name as coachName, "
                               + "schedule.signin_time as signinTime, "
                               + "schedule.course_id as courseId, "
                               + "schedule.site_id as siteId, "
                               + "schedule.id as courseScheduleId, "
                               + "schedule.course_type as courseType, "
                               + "attr.value as courseAmount, "
                               + "course.hours as courseHours "
                        + "FROM orders orders, course_schedule schedule, course_round_attr attr, "
                             + "student student, course_round round, principal coach, course course "
                        + "WHERE schedule.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "round.id = attr.course_round_id AND "
                              + "schedule.course_round_id = round.id AND "
                              + "schedule.coach_id = coach.id AND "
                              + "attr.name='" + Constant.FINISHED_COURSE_REPORT_COURSE_AMOUNT + "' AND "
                              + "schedule.course_id = course.id AND "
                              + "schedule.is_disabled = false AND "
                              + "schedule.occur_date IS NOT NULL AND "
                              + "schedule.signin_time IS NOT NULL AND "
                              + "schedule.course_type in (" + Constant.FINISHED_COURSE_REPORT_COURSE_TYPE + ") AND "
                              + "schedule.schedule_status = " + Constant.FINISHED_COURSE_REPORT_SCHEDULE_STATUS + " AND "
                              + "schedule.signin_time BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(start, end), FinishedCourseReport.class);
    }
    
    /**
     * 
     * @return
     */
    private List<FinishedCourseReport> getYearScheduleReports() {
        
        String sentence = "SELECT orders.price as price, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "round.course_id as courseId, "
                               + "round.site_id as siteId, "
                               + "round.course_type as courseType, "
                               + "round.effective_start as effectiveStart, "
                               + "round.effective_end as effectiveEnd "
                        + "FROM orders orders, student student, course_round round "
                        + "WHERE orders.id = round.order_id AND "
                              + "round.student_id = student.id AND "
                              + "round.is_disabled = false AND "
                              + "round.course_type = " + Constant.FINISHED_COURSE_REPORT_YEAR_TYPE + " AND "
                              + "round.course_status = " + Constant.FINISHED_COURSE_REPORT_ROUND_STATUS;
        
        return genericJdbcSupport.queryForList(sentence, FinishedCourseReport.class);
    }
    
    /**
     * 
     * @return
     */
    private Map<String, Object> generateYesterdayParams() {
    	
		Date yesterday = DateUtils.addDays(new Date(), -1);
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("beginDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return parameters;
    }
    
    private Map<String, Object> generateYesterdayParams(Date day) {
        
        Date yesterday = DateUtils.addDays(day, -1);
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("beginDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return parameters;
    }
    
    private Map<String, Object> generateYesterdayParams(Date start, Date end) {
        
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("beginDate", DateUtils.parseDate(DateFormatUtils.format(start, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endDate", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return parameters;
    }
}
